<?php

class CustomerReportAction extends SecurityAction{
	function _initialize(){
        $this->assign("menuFirst",session('menuFirst')); 
        $this->assign("menuSecond",session('menuSecond')); 
    }
		
	function clist(){
		$where = array();
		
		$shopid = $_GET["shopid"];
		$type = $_GET["ctype"]; 

		$sqlStr = "select a.*,b.shopname,(select count(*) from yq_order where customid = a.tid) number from yq_custom a, yq_shop b where a.shopid = b.tid ";  //is_forbit = '1' and

		if($shopid){
		    $where['shopid'] = array('eq',$shopid);
		    $sqlStr = $sqlStr." and a.shopid = ".$shopid;
		}
		if($type != ''){
		    $where['ctype'] = array('eq',$type);
		    $sqlStr = $sqlStr." and a.ctype = ".$type;
		}
		
		$ob = M("yq_custom");

		$cpp = 20; //分页数
        import('ORG.Util.Page');// 导入分页类
        $count      = $ob->where($where)->count();// 查询满足要求的总记录数
        $Page       = new Page($count,$cpp);// 实例化分页类 传入总记录数和每页显示的记录数
        $show       = $Page->show();// 分页显示输出
        
        $query = M("");

        $sqlStr = $sqlStr." limit ".$Page->firstRow.','.$Page->listRows;
		$list = $query->query($sqlStr);

		$shopOb = M("yq_shop");
		$shoplist = $shopOb->where(array("is_forbit"=>"1"))->select();

		$this->assign("list", $list);
		$this->assign("shoplist", $shoplist);
		$this->assign('show',$show);// 赋值分页输出

		$this->display("Report/clist");
	}
	
	
	//7天未消费客户统计
	function consumed(){
		$where = array();
		$stime = $_GET['stime'];
		$etime = $_GET['etime'];
		$shopid = $_GET["shopid"];
		
		if($stime && $etime){
			
		}else{
			$stime = date('Y-m-d', strtotime('-15 days'));
			$etime = date("Y-m-d", time());
		}
		
		if($shopid){
			$sql = "and yq_custom.shopid = '{$shopid}'";
		}
		$ob = M("yq_custom");

		$cpp = 20; //分页数
        import('ORG.Util.Page');// 导入分页类
        $count      = $ob->join(" yq_shop on yq_custom.shopid = yq_shop.tid ")
        				 ->where(" not exists (select * from yq_order b 
        				 		where b.customid = yq_custom.tid and b.created_at >= '".$stime."' 
        				 			and b.created_at<=  '".$etime."' ) $sql")->count();// 查询满足要求的总记录数
        $Page       = new Page($count,$cpp);// 实例化分页类 传入总记录数和每页显示的记录数
        $show       = $Page->show();// 分页显示输出
        
		$list = $ob->join(" yq_shop on yq_custom.shopid = yq_shop.tid ")
				   ->where(" not exists (select * from yq_order b where b.customid = yq_custom.tid and b.created_at >= '".$stime."' 
									and b.created_at<=  '".$etime."' )
				   		$sql")
				   ->limit($Page->firstRow.','.$Page->listRows)->select();

		
		$this->assign("list", $list);
		$this->assign('show',$show);// 赋值分页输出

		$shopOb = M("yq_shop");
		$shoplist = $shopOb->where(array("is_forbit"=>"1"))->select();
		$this->assign("shoplist", $shoplist);
		
		$this->assign("stime", $stime);
		$this->assign("etime", $etime);
		$this->display("Report/consumed");
		
	}

	//未消费客户统计
	function consumed2(){
		$now = date('Y-m-d', time());
		$oneweek = date('Y-m-d', strtotime('-1 week'));
		$twoweek = date('Y-m-d', strtotime('-2 week'));
		$threeweek = date('Y-m-d', strtotime('-3 week'));

		$onemonth= date("Y-m-d", strtotime("-1 month")); 
		$twomonth= date("Y-m-d", strtotime("-2 month")); 
		$threemonth= date("Y-m-d", strtotime("-3 month")); 
		$fourmonth= date("Y-m-d", strtotime("-4 month")); 
		$fivemonth= date("Y-m-d", strtotime("-5 month")); 
		$sixmonth= date("Y-m-d", strtotime("-6 month")); 
		$ninemonth= date("Y-m-d", strtotime("-9 month")); 

		$oneyear= date("Y-m-d", strtotime("-1 year")); 
		$twoyear= date("Y-m-d", strtotime("-2 year")); 
		
		$shopOb = M("yq_shop");
		$shoplist = $shopOb->where(array("is_forbit"=>"1"))->select();
		$this->assign("shoplist", $shoplist);
		
		$shopid = $_GET["shopid"];
		if($shopid){
			$sql = "and c.shopid = '{$shopid}'";
		}

		$sqlStr1 = "select  ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$oneweek."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select  ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$twoweek."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select  ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$threeweek."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$onemonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$twomonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$threemonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$fourmonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$fivemonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select    ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$sixmonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$ninemonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$oneyear."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype = '1' ) $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$twoyear."'  and b.created_at <= '".$now."')
			group by ctype
		
								";

		$sqlStr2 = "select  ifnull(count(*),0) number ,ctype from yq_custom c where  ( c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$oneweek."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select  ifnull(count(*),0) number ,ctype from yq_custom c where  ( c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$twoweek."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select  ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$threeweek."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  ( c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$onemonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  ( c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$twomonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$threemonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  ( c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$fourmonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  (c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$fivemonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select    ifnull(count(*),0) number ,ctype from yq_custom c where  ( c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$sixmonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  ( c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$ninemonth."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  ( c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$oneyear."'  and b.created_at <= '".$now."')
			group by ctype
			union all
			select   ifnull(count(*),0) number ,ctype from yq_custom c where  ( c.ctype='2') $sql
			and not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$twoyear."'  and b.created_at <= '".$now."')
			group by ctype
		
								";

		$ob = M("");
		$list1 = $ob->query($sqlStr1);

		$list2 = $ob->query($sqlStr2);
        
	    $date[] = '1周';
	    $date[] = '2周';
	    $date[] = '3周';
	    $date[] = '1月';
	    $date[] = '2月';
	    $date[] = '3月';
	    $date[] = '4月';
	    $date[] = '5月';
	    $date[] = '6月';
	    $date[] = '9月';
	    $date[] = '1年';
	    $date[] = '2年';
        
	    $sst = array(
	            $list1[0]['number'], $list1[1]['number'], $list1[2]['number'], $list1[3]['number'], $list1[4]['number'], $list1[5]['number'], $list1[6]['number'], $list1[7]['number'], $list1[8]['number'], $list1[9]['number'], $list1[10]['number'], $list1[11]['number']
	        );
	    
	    $tmp = array();
	    foreach($sst as $value1){
	        $tmp[] = intval($value1);
	    }

	    $normal = array('normal'=>array('show'=>'true','position'=>'top'));

	    $value[] = array(
	        'name' => '粉丝客',
	        'type' => 'bar',
	        'label' => $normal,
	        'data' => $tmp,
	    );
	    
	   $sst2 = array(
	            $list2[0]['number'], $list2[1]['number'], $list2[2]['number'], $list2[3]['number'], $list2[4]['number'], $list2[5]['number'], $list2[6]['number'], $list2[7]['number'], $list2[8]['number'], $list2[9]['number'], $list2[10]['number'], $list2[11]['number']
	        );
	    
	    $tmp1 = array();
	    foreach($sst2 as $value1){
	        $tmp1[] = intval($value1);
	    }
	    $value[] = array(
	        'name' => '老客',
	        'type' => 'bar',
	        'label' => $normal,
	        'data' => $tmp1,
	    );
	    
	    $time = $_GET['time'];
	    $fromTime = $oneweek;
	    if(empty($time)){
	        $time = "oneweek";
	    }
	    eval('$fromTime = $'.$time.';');
	    $ob = M();
// 	    $sql = "select b.* from yq_order a left join yq_custom b on a.customid = b.tid
// 	         where a.created_at <='".date('Y-m-d',time()-86400*$time)."'
// 			group by b.tid";

	    $sql = "select ctype , c.* from yq_custom c where ( ctype = 1 or ctype = 2 ) $sql and
not exists (select * from yq_order b where b.customid = c.tid and b.created_at >='".$fromTime."' 
	        and b.created_at <= '".$now."')
	    ";
	    $list = $ob->query($sql);
	    
	    $this->assign('fromTime',$time);
	    $this->assign('list',$list);
	    $this->assign('date',json_encode($date));
	    $this->assign('value',json_encode($value));
        
        $this->display("Report/consumed2");
	}
	


	function getCustomerCountByNoneConsume($day){
	    
	}
	
	//项目分类消费次数
	function consumed3back(){
	    $date[] = '1月';
	    $date[] = '2月';
	    $date[] = '3月';
	    $date[] = '4月';
	    $date[] = '5月';
	    $date[] = '6月';
	    $date[] = '7月';
	    $date[] = '8月';
	    $date[] = '9月';
	    $date[] = '10月';
	    $date[] = '11月';
	    $date[] = '12月';


/*	    $onemonth = date('Y-01-01');
	    $onemonthlast = date('Y-m-d', strtotime("$onemonth +1 month -1 day"));  
	    $twomonth = date('Y-02-01');
		$twomonthlast = date('Y-m-d', strtotime("$twomonth +1 month -1 day"));  
		$threemonth = date('Y-03-01');
	    $threemonthlast = date('Y-m-d', strtotime("$threemonth +1 month -1 day"));  
	    $fourmonth = date('Y-04-01');
		$fourmonthlast = date('Y-m-d', strtotime("$fourmonth +1 month -1 day"));  
		$fivemonth = date('Y-05-01');
	    $fivemonthlast = date('Y-m-d', strtotime("$fivemonth +1 month -1 day"));  
	    $sixmonth = date('Y-06-01');
		$sixmonthlast = date('Y-m-d', strtotime("$sixmonth +1 month -1 day"));  
		$sevenmonth = date('Y-07-01');
	    $sevenmonthlast = date('Y-m-d', strtotime("$sevenmonth +1 month -1 day"));  
	    $eightmonth = date('Y-08-01');
		$eightmonthlast = date('Y-m-d', strtotime("$eightmonth +1 month -1 day"));  
		$ninemonth = date('Y-09-01');
	    $ninemonthlast = date('Y-m-d', strtotime("$ninemonth +1 month -1 day"));  
	    $tenmonth = date('Y-10-01');
		$tenmonthlast = date('Y-m-d', strtotime("$tenmonth +1 month -1 day"));  
		$elevenmonth = date('Y-11-01');
	    $elevenmonthlast = date('Y-m-d', strtotime("$elevenmonth +1 month -1 day"));  
	    $twevelmonth = date('Y-12-01');
		$twevelmonthlast = date('Y-m-d', strtotime("$twevelmonth +1 month -1 day"));  

	    $sqlStr = "
					select count(prono) number,pname, type,'1' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and 
					b.created_at >='".$onemonth."'  and b.created_at <= '".$onemonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'2' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and
					b.created_at >='".$twomonth."'  and b.created_at <= '".$twomonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'3' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and 
					b.created_at >='".$threemonth."'  and b.created_at <= '".$threemonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'4' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and
					b.created_at >='".$fourmonth."'  and b.created_at <= '".$fourmonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'5' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and 
					b.created_at >='".$fivemonth."'  and b.created_at <= '".$fivemonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'6' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and 
					b.created_at >='".$sixmonth."'  and b.created_at <= '".$sixmonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'7' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and 
					b.created_at >='".$sevenmonth."'  and b.created_at <= '".$sevenmonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'8' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and 
					b.created_at >='".$eightmonth."'  and b.created_at <= '".$eightmonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'9' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and 
					b.created_at >='".$ninemonth."'  and b.created_at <= '".$ninemonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'10' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and 
					b.created_at >='".$tenmonth."'  and b.created_at <= '".$tenmonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'11' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and 
					b.created_at >='".$elevenmonth."'  and b.created_at <= '".$elevenmonthlast."') group by type
					union all 
					select count(prono) number,pname, type,'12' as month from  yq_product a 
					where  exists (select proid from yq_ordergoods b where a.tid = b.proid and 
					b.created_at >='".$twevelmonth."'  and b.created_at <= '".$twevelmonthlast."') group by type";
	    $ob = M("");
	    $list = $ob->query($sqlStr);

		$const_type = array('0'=>'美容项目','1'=>'头发护理','2'=>'染发项目','3'=>'烫发项目');
		foreach($const_type as $key=>$value){
			$reslut[$key] = $this->resetData($key,$list);
		}*/

		// var_dump($reslut);
	    
	    $value[0] = array(

	        'name' => '美容项目',
	        'type' => 'bar',
	        'data' => array(
	            
	        ),
	    );
	    $value[1] = array(
	        'name' => '头发护理',
	        'type' => 'bar',
	        'data' => array(
	            
	        ),
	    );
	    $value[2] = array(
	        'name' => '染发项目',
	        'type' => 'bar',
	        'data' => array(
	            
	        ),
	    );
	    $value[3] = array(
	        'name' => '烫发项目',
	        'type' => 'bar',
	        'data' => array(
	            
	        ),

	    );

	    
	    $year = '2016';
	    for($i=1;$i<13;$i++){
	        $tmp = $this->getProjectCount($year, $i);
	        $value[0]['data'][] = intval($tmp[0]['number']);
	        $value[1]['data'][] = intval($tmp[1]['number']);
	        $value[2]['data'][] = intval($tmp[2]['number']);
	        $value[3]['data'][] = intval($tmp[3]['number']);
	    }

// 	            头发护理 染发项目 烫发项目
	    
    	$this->assign('date',json_encode($date));
    	$this->assign('date_array',($date));
    	$this->assign('value',json_encode($value));
    	$this->assign('value_array',($value));
    	
    	$this->display("Report/consumed3");
	
	}

	function consumed3(){
	    $year = "";
	    $month = "";
	    $shopname = "";

	    $shopid = $_GET["shopid"];
	    if ($shopid){
	    	$ob2 = M("yq_shop");
	        $shopVal = $ob2->where(array("tid"=>$shopid))->select();
		    $shopname =  $shopVal[0]['shopname'];
	    }

	    if ($_GET["year"]){
	    	$year = $_GET["year"];
	    }else{
	    	$year = date("Y", time());
	    }
	    if ($_GET["month"]){
	    	$month = $_GET["month"];
	    }else{
	    	$month = date("m", time());
	    }

	    $list = $this->getProjectCount($year, $month, $shopid);

	    $b = M("yq_dict");
	    $typelist = $b->where(array("type"=>"product"))->select();

	    foreach($typelist as $key1 => $value1){
	    	foreach ($list as $key2 => $value2) {
	    		if ($value2['type'] == $value1['code']){
		   			$shopname = $value2['shopname'];
		   			$value[] = array(
				        'name' => $value1["value"],
				        'number' => $value2['number'],
				        'type' => $value2['type'],
				        'shopname' => $value2['shopname'],
				        'tid' => $value2['tid'],
				        'typename' => $value1['value'],
				        'yearmonth' => $value2['yearmonth'],
				    );
		   		}/*else{
		   			if ($value['name'] != $value1["value"]){
			   			$value[] = array(
					        'name' => $value1["value"],
					        'number' => '0',
					        'type' => '',
					        'shopname' => $shopname,
					        'tid' => '',
					        'typename' => $value1['value'],
					        'yearmonth' => $year.'-'.$month,
					    );
			   		}
		   		}*/
	    	}
		    
		}
// print_r($value);
// 	    头发护理 染发项目 烫发项目
        $ob = M("yq_shop");
        $shoplist = $ob->where(array("is_forbit"=>'1'))->select();
    	
    	$this->assign('list',$value);
    	$this->assign('shoplist',$shoplist);
    	
    	$this->display("Report/consumed3");
	
	}
	
	function getProjectCount($year,$month,$shopid){
	    $month = str_repeat('0', 2-strlen($month)).$month;
	    $sql = "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '{$year}-{$month}' as yearmonth 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	                       where a.created_at like '{$year}-{$month}%' ";

	    if (!empty($shopid)) {
	    	 $sql .= " and c.tid = '{$shopid}'";
	    }                  
	    $sql .= "group by b.type ";

	    $list = M()->query($sql);
	    return $list;
	}
	
	//会员卡分类汇总
	function consumed4(){
	    
	    $ob = M("yq_shop");
	    $shoplist = $ob->where(array("is_forbit"=>'1'))->select();
	    
	    $ob = M('yq_card');
	    $clist = $ob->order(array('cid'=>'asc'))->select();    
	    $date = array();
	    
	    $year = date('Y');
	    if($_GET['year']) {
	        $year = $_GET['year'];
	    }
	 	$month = date('m');
	 	
	 	if ($_GET['month']){
	 		 $month = $_GET['month'];
	 	}

        for($i=$month;$i<=$month;$i++){
            $date[$i] = array('name'=>$i.'月','data'=>array());
        }
        for($i=$month;$i<=$month;$i++){
            $tmp = $this->getCardCount($year, $i,$_GET['shopid']);
            foreach($clist as $key=>$value){
                $date[$i]['data'][$value['cid']] = intval($tmp[$value['cid']]['number']);
            }
        } 

// 	                筛选全部
// 	    $date = array();
// 	    for($i=1;$i<13;$i++){
// 	        $date[$i] = array('name'=>$i.'月','data'=>array());
// 	    }
// 	    for($i=1;$i<13;$i++){
// 	        $tmp = $this->getCardCount($year, $i);
// 	        foreach($clist as $key=>$value){
// 	            $date[$i]['data'][$value['cid']] = intval($tmp[$value['cid']]['number']);
// 	        }
// 	    }
	    $this->assign('year',($year));
	    $this->assign('date_array',($date));
	    $this->assign('clist',($clist));
	    $this->assign('shoplist',($shoplist));
	    $this->display("Report/consumed4");
	}
	
	function getCardCount($year,$month,$shopid=''){
	    $month = str_repeat('0', 2-strlen($month)).$month;
	    if($shopid!=''){
	        $shopsql = "and c.shopid = '{$shopid}'";
	    }
	    $sql = "SELECT ifnull(count(*),0) number,hyk ,b.cardname
	               FROM `yq_ordergoods` a 
	                   left join yq_card b on a.hyk=b.cid
	                   left join yq_order c on a.order_id = c.tid
                	       where a.created_at like '{$year}-{$month}%' $shopsql
                	           group by hyk ";
	    $list = M()->query($sql);
	    foreach($list as $key=>$value){
	        $tmp[$value['hyk']] = $value;
	    }
	    return $tmp;
	}


	function resetData($type , $list){
		$const_type = array('0'=>'美容项目','1'=>'头发护理','2'=>'染发项目','3'=>'烫发项目');
		foreach($list as $key=>$value){
			if($value['type']==$type){
				$tmp['name'] = $const_type[$value['type']];
				$tmp['type'] = 'bar';
				$tmp['data1'][$value['month']] = $value['number'];
			}
		}
		for($i=1;$i<=12;$i++){
			if(!isset($tmp['data1'][$i])){
				$tmp['data1'][$i] = 0;
			}
		}
		ksort($tmp['data1']);
		foreach($tmp['data1'] as $key1=>$value1){
			$tmp['data'][] = $value1;
		}
		unset($tmp['data1']);
		return $tmp;
	}


	//跟单客户经理统计
	function consumed5(){
		

		$shopOb = M("yq_shop");
		$shoplist = $shopOb->where(array("is_forbit"=>"1"))->select();

		$obb = M("yq_member");
		$managerlist = $obb->where(
				array('gradeid'=>'6','is_forbit'=>'1','shopid'=>$_GET["shopid"])
				)->select();
			
		$this->assign("shoplist", $shoplist);
		$this->assign("managerlist", $managerlist);

		if (!$_GET['managerid']){ //跟单经理查询条件必填
			$this->display("Report/consumed5");
			return;
		}

		$managerid = $_GET['managerid'];
		$shopid = $_GET['shopid'];
	    if ($_GET["year"]){
	    	$year = $_GET["year"];
	    }else{
	    	$year = date("Y", time());
	    }
	    if ($_GET["month"]){
	    	$month = $_GET["month"];
	    }else{
	    	$month = date("m", time());
	    }

	    $obb2 = M("yq_member");
		$nameInfo = $obb2->where(array('tid'=>$managerid))->find();

	    //构造报表查询sql
		$sqlStr = " SELECT ifnull(count(*),0) number,
							sum(a.price) price,
							sum(a.discount_price) discount_price,
							c.shopname,c.tid,b.realname, 
							'{$year}-{$month}' as yearmonth  
					FROM yq_order a 
					left join yq_member b on a.managerid = b.tid and b.gradeid ='6'
					left join yq_shop c on c.tid = a.shopid
					where a.created_at like '{$year}-{$month}%' "; 

		if($shopid){
		    $sqlStr = $sqlStr." and a.shopid = ".$shopid;
		}
		if($managerid){
		    $sqlStr = $sqlStr." and a.managerid like '%,".$managerid.",%'";
		}
		$sqlStr .= " group by a.managerid ";

		//构造报表分页sql
		$countSql = " SELECT COUNT(*) NUM FROM ({$sqlStr}) A";
		$count = M()->query($countSql);

		$cpp = 20; //分页数
        import('ORG.Util.Page');// 导入分页类
        $Page       = new Page($count[0]['NUM'],$cpp);
        $show       = $Page->show();// 分页显示输出
	

        $sqlStr = $sqlStr." limit ".$Page->firstRow.','.$Page->listRows;
		$list = M()->query($sqlStr);

		$this->assign("list", $list);
		$this->assign("shoplist", $shoplist);
		$this->assign('show',$show);// 赋值分页输出
		$this->assign('nameInfo',$nameInfo); //跟单经理名称
		
		$this->display("Report/consumed5");
		
		
	}


	//项目分类消费次数
	function consumed6(){
		$shopid = $_GET["shopid"];
		$producttype = $_GET["produttype"];
	    if ($_GET["year"]){
	    	$year = $_GET["year"];
	    }else{
	    	$year = date("Y", time());
	    }

	    $list = $this->doquery($year,$shopid,$producttype);

	    $date[] = '1月';
	    $date[] = '2月';
	    $date[] = '3月';
	    $date[] = '4月';
	    $date[] = '5月';
	    $date[] = '6月';
	    $date[] = '7月';
	    $date[] = '8月';
	    $date[] = '9月';
	    $date[] = '10月';
	    $date[] = '11月';
	    $date[] = '12月';

	    $normal = array('normal'=>array('show'=>'true','position'=>'top'));
	    $value[0] = array(
			        'name' => '',
			        'type' => 'bar',
			        'label'=> $normal,
			        'data' => array(),
			    );
	    $value[1] = array(
			        'name' => '',
			        'type' => 'bar',
			        'label'=> $normal,
			        'data' => array(),
			    );
	    $value[2] = array(
			        'name' => '',
			        'type' => 'bar',
			        'label'=> $normal,
			        'data' => array(),
			    );
	    $value[3] = array(
			        'name' => '',
			        'type' => 'bar',
			        'label'=> $normal,
			        'data' => array(),
			    );
	    $value[4] = array(
			        'name' => '',
			        'type' => 'bar',
			        'label'=> $normal,
			        'data' => array(),
			    );
	    $value[5] = array(
			        'name' => '',
			        'type' => 'bar',
			        'label'=> $normal,
			        'data' => array(),
			    );
	    $value[6] = array(
			        'name' => '',
			        'type' => 'bar',
			        'label'=> $normal,
			        'data' => array(),
			    );
	    $value[7] = array(
			        'name' => '',
			        'type' => 'bar',
			        'label'=> $normal,
			        'data' => array(),
			    );
        
	    foreach ($list as $key1 => $value1) {
	    	//线状图显示数量
	        if ($list[$key1]['type'] == '0'){
	        	$value[0]["name"] = $list[$key1]['typename'];
	        	$value[0]["data"][] = intval($list[$key1]['number']);
	        }else if ($list[$key1]['type'] == '1'){
	        	$value[1]["name"] = $list[$key1]['typename'];
	        	$value[1]["data"][] = intval($list[$key1]['number']);
	        }else if ($list[$key1]['type'] == '2'){
	        	$value[2]["name"] = $list[$key1]['typename'];
	        	$value[2]["data"][] = intval($list[$key1]['number']);
	        }else if ($list[$key1]['type'] == '3'){
	        	$value[3]["name"] = $list[$key1]['typename'];
	        	$value[3]["data"][] = intval($list[$key1]['number']);
	        }else if ($list[$key1]['type'] == '4'){
	        	$value[4]["name"] = $list[$key1]['typename'];
	        	$value[4]["data"][] = intval($list[$key1]['number']);
	        }else if ($list[$key1]['type'] == '5'){
	        	$value[5]["name"] = $list[$key1]['typename'];
	        	$value[5]["data"][] = intval($list[$key1]['number']);
	        }else if ( $list[$key1]['type'] == '6'){
	        	$value[6]["name"] = $list[$key1]['typename'];
	        	$value[6]["data"][] = intval($list[$key1]['number']);
	        }else if ($list[$key1]['type'] == '7'){
	        	$value[7]["name"] = $list[$key1]['typename'];
	        	$value[7]["data"][] = intval($list[$key1]['number']);	      
	        }
	    }

	    $shopOb = M("yq_shop");
		$shoplist = $shopOb->where(array("is_forbit"=>"1"))->select();

		$productob = M("yq_dict");
		$productlist = $productob->where(array("type"=>"product"))->select();

		$this->assign("shoplist", $shoplist);
		$this->assign("productlist", $productlist);
	    
    	$this->assign('date',json_encode($date));
    	$this->assign('date_array',($data));
    	$this->assign('value',json_encode($value));
//  var_dump($value);   	
// var_dump(json_encode($value));
    	$this->assign('value_array',($data));
    	
    	$this->display("Report/consumed6");
	
	}

	function doquery($year,$shopid,$type){
		if (isset($shopid) && $shopid != '') {
	    	 $whereSql .= " and c.tid = '{$shopid}'";
	    } 
	    if (isset($type) && $type != '') {
	    	 $whereSql .= " and b.type = '{$type}'";
	    }    
	    if (empty($year)) {
	    	$year = date('Y');
	    }

	    $sql = "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '1' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-01%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '2' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-02%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '3' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-03%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '4' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-04%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '5' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-05%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '6' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-06%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '7' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-07%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '8' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-08%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '9' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-09%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '10' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-10%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '11' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-11%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";
	    $sql .= " union all ";
	    $sql .= "SELECT ifnull(count(*),0) number,b.type ,c.shopname,c.tid,
	    		(select value from yq_dict where type='product' and code = b.type) as typename, '12' as month 
	               FROM yq_ordergoods a 
	               left join yq_product b on a.proid = b.tid 
	               left join yq_order d on d.tid = a.order_id
	               left join yq_shop c on c.tid = d.shopid
	               where a.created_at like '{$year}-12%' ";
	    $sql .=  $whereSql;           
	    $sql .= "group by b.type ";

	    $list = M()->query($sql);
	    return $list;
	}

}

?>